﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using BadmintonApp.Entity;
using BadmintonApp.DAO.Interface;

namespace BadmintonApp.DAO.Implement
{
    public class UsersOfRoleDAO : BaseDAO, IUsersOfRoleDAO
    {

        private int GetMaxID()
        {
            string sql = "Select IsNull(Max(FID), 0) from TUsersOfRole";
            DbCommand selectCmd = db.GetSqlStringCommand(sql);
            return (int)db.ExecuteScalar(selectCmd);
        }

        #region IUsersOfRoleDAO Members

        /// <summary>
        /// 插入用户与角色的对应关系
        /// </summary>
        /// <param name="usersOfRoleInfo">用户与角色对应关系信息类</param>
        public void InsertUsersOfRole(TUsersOfRoleInfo usersOfRoleInfo)
        {
            string sql = "Insert into TUsersOfRole(FID, FUserID, FRoleID) Values (@FID, @FUserID, @FRoleID)";
            DbCommand insertCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(insertCmd, "FID", DbType.Int32, usersOfRoleInfo.FID);
            db.AddInParameter(insertCmd, "FUserID", DbType.Int32, usersOfRoleInfo.FUserID);
            db.AddInParameter(insertCmd, "FRoleID", DbType.Int32, usersOfRoleInfo.FRoleID);
            db.ExecuteNonQuery(insertCmd);
        }

        /// <summary>
        /// 更新用户与角色的对应关系
        /// </summary>
        /// <param name="usersOfRoleInfo">用户与角色对应关系信息类</param>
        public void UpdateUsersOfRole(TUsersOfRoleInfo usersOfRoleInfo)
        {
            string sql = "Update TUsersOfRole set FUserID=@FUserID, FRoleID=@FRoleID Where FID=@FID";
            DbCommand updateCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(updateCmd, "FID", DbType.Int32, usersOfRoleInfo.FID);
            db.AddInParameter(updateCmd, "FUserID", DbType.Int32, usersOfRoleInfo.FUserID);
            db.AddInParameter(updateCmd, "FRoleID", DbType.Int32, usersOfRoleInfo.FRoleID);
            db.ExecuteNonQuery(updateCmd);
        }

        /// <summary>
        /// 删除用户与角色的对应关系
        /// </summary>
        /// <param name="id">关系ID</param>
        public void DeleteUsersOfRole(int id)
        {
            string sql = "Delete from TUsersOfRole Where FID=@FID";
            DbCommand deleteCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(deleteCmd, "FID", DbType.Int32, id);
            db.ExecuteNonQuery(deleteCmd);
        }

        /// <summary>
        /// 根据用户ID获取用户与角色的对应关系列表
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns>用户与角色的对应关系列表</returns>
        public DataSet GetUsersOfRoleByUserID(int userID)
        {
            string sql = "Select * from TUsersOfRole Where FUserID=@FUserID";
            DbCommand selectCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(selectCmd, "FUserID", DbType.Int32, userID);
            DataSet dsUsersOfRole = db.ExecuteDataSet(selectCmd);
            return dsUsersOfRole;
        }

        /// <summary>
        /// 根据角色ID获取用户与角色的对应关系列表
        /// </summary>
        /// <param name="roleID">角色ID</param>
        /// <returns>用户与角色的对应关系列表</returns>
        public DataSet GetUsersOfRoleByRoleID(int roleID)
        {
            string sql = "Select * from TUsersOfRole Where FRoleID=@FRoleID";
            DbCommand selectCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(selectCmd, "FRoleID", DbType.Int32, roleID);
            DataSet dsUsersOfRole = db.ExecuteDataSet(selectCmd);
            return dsUsersOfRole;
        }

        /// <summary>
        /// 根据用户ID和角色ID获取用户与角色的对应关系列表
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="roleID">角色ID</param>
        /// <returns>用户与角色对应关系信息类</returns>
        public TUsersOfRoleInfo GetUsersOfRoleByUserIDAndRoleID(int userID, int roleID)
        {
            string sql = "Select * from TUsersOfRole Where FUserID=@FUserID and FRoleID=@FRoleID";
            TUsersOfRoleInfo usersOfRoleInfo = null;
            DbCommand selectCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(selectCmd, "FUserID", DbType.Int32, userID);
            db.AddInParameter(selectCmd, "FRoleID", DbType.Int32, roleID);
            DataSet dsUsersOfRole = db.ExecuteDataSet(selectCmd);
            if (dsUsersOfRole.Tables.Count > 0 && dsUsersOfRole.Tables[0] != null)
            {
                DataRow dr = dsUsersOfRole.Tables[0].Rows[0];
                if (dr == null)
                    return usersOfRoleInfo;

                usersOfRoleInfo.FID = Convert.ToInt32(dr["FID"]);
                usersOfRoleInfo.FRoleID = Convert.ToInt32(dr["FRoleID"]);
                usersOfRoleInfo.FUserID = Convert.ToInt32(dr["FUserID"]);
            }
            return usersOfRoleInfo;
        }

        #endregion
    }
}
